/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Reportes.EnvioDestiempo;
import Reportes.HoraExtra;
import Model.Parametro;
import Model.Usuario;
import Presenter.ParametroPresenter;
import Principals.Main;
import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import org.hibernate.Query;

/**
 *
 * @author CARLOS
 */
public class EnvioDestimepoDAO {
     public static List<EnvioDestiempo> buscarEnvioDestiempo(Date fechaInicio, Date fechaFin) {
        String q = "select  fecha, em.nombres \"nombre\",c.nombres\"cliente\",nombre \"producto\",horas,cast(totalpenalidad as float) \"penalidad\",en.id_envio_pedido,id_pedido  from (\n" +
"select p.id_cliente,p.id_envio_pedido,p.id_pedido, date_trunc('day', p.fecha_registro) \"fecha\",pr.nombre,(CASE \n" +
"WHEN (( cast( ((extract (epoch from ( cast(fecha_entrega as timestamp) - cast(fecha_registro as timestamp))))) as integer) )-86400 )%3600 <=0 then 0 else \n" +
"(( cast(((extract (epoch from (  CAST(fecha_entrega as timestamp) - cast(fecha_registro   as timestamp))))) as integer) )-86400 )/3600 +1 END) \"horas\",\n" +
" (CASE \n" +
"WHEN (( cast( ((extract (epoch from ( cast(fecha_entrega as timestamp) - cast(fecha_registro as timestamp))))) as integer) )-86400 )%3600 <=0 then 0 else \n" +
"(( cast(((extract (epoch from (  CAST(fecha_entrega as timestamp) - cast(fecha_registro   as timestamp))))) as integer) )-86400 )/3600 +1 END)*\n" +
"(select valor_numerico from parametro where nombre='Costo por hora de retraso') \"totalpenalidad\" from pedido p\n" +
"inner join producto pr \n" +
"on pr.id_producto=p.id_producto\n" +
"\n" +
"where\n" +
" (( cast( ((extract (epoch from ( cast(fecha_entrega as timestamp) - cast(fecha_registro as timestamp))))) as integer) )-86400 )%3600 >0  and \n" +
"p.estado>=1 and fecha_entrega is not null and date_trunc('day', fecha_registro)\n" +
"  between date_trunc('day', cast(:fechaInicio as timestamp))  and date_trunc('day', cast(:fechaFin  as timestamp))\n" +
"\n" +
" )  \"t\" \n" +
"inner join cliente c on t.id_cliente=c.id_cliente\n" +
"inner join envio_pedido en on en.id_envio_pedido=t.id_envio_pedido\n" +
"inner join empleado em on en.id_empleado=em.id_empleado\n" +
"order by 1 desc\n" +
"";
        //  int cont = 0;
        //   q += " where fecha between :fechaInicio and :fechaFin ";
      

        Main.usrSesion.session.beginTransaction();
        Query query = Main.usrSesion.session.createSQLQuery(q);

          query.setParameter("fechaInicio", fechaInicio);
          query.setParameter("fechaFin", fechaFin);

        Main.usrSesion.session.getTransaction().commit();
        List<Object[]> b = query.list();

        List<EnvioDestiempo> a = new LinkedList<EnvioDestiempo>();
EnvioDestiempo h = null;
SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-ddHHmmss");


        for (Object[] o : b) {
       try{
            Date fecha=new Date();
            
            fecha= (Date) o[0];
            String aa,bb,cc;
            aa= (String) o[1];
            bb=String.valueOf( o[2]);
            cc=String.valueOf( o[3]);
            
            int d;
            float e;
            
            
            d=  ((Integer)( o[4]));
            
            
            
            
            e=((Double)o[5]).floatValue();
           
            int idPedido,idEnvio;
            
            idEnvio=d=  ((Integer)( o[6]));
            idPedido=d=  ((Integer)( o[7]));
            
            h=new EnvioDestiempo(fecha,aa,bb , cc, d,e,idEnvio,idPedido);
            a.add(h);
            
       }catch(Exception d){}
        }



        return a;
    }
}
